<?php
///
namespace lxsphp\Store\Pdo\Command;

/**
 * 表示一个针对数据库执行的SQL语句。
 *
 * @version $Id: Command.php 194 2014-07-18 06:22:47Z linsir123 $
 * @package lxsphp\Store\Pdo\Command
 */
class Command
{
	/**
	 * @var \lxsphp\Store\Pdo\Pdo
	 */
	public $pdo;

	/**
	 * SQL数组
	 *
	 * @var array
	 */
	public $query;

	/**
	 * SQL
	 *
	 * @var string
	 */
	public $text;

	/**
	 * 用于绑定的参数
	 *
	 * @var array
	 */
	public $params = array();

	/**
	 * 构造函数
	 *
	 * @param \lxsphp\Store\Pdo\Pdo $pdo
	 */
	public function __construct($pdo)
	{
		///
		$this->pdo = $pdo;
	}

	/**
	 * 取得构造得到的SQL
	 *
	 * @return string
	 */
	public function getText()
	{
		///
		if ($this->text == '' && ! empty($this->query))
			$this->setText($this->_buildQuery());

		return $this->text;
	}

	/**
	 * 设置SQL
	 *
	 * @param string $value
	 *
	 * @return Command
	 */
	public function setText($value)
	{
		///
		$this->text = $value;

		return $this;
	}

	/**
	 * 重置
	 *
	 * @return Command
	 */
	public function reset()
	{
		///
		$this->text = null;
		$this->query = null;
		$this->params = array();

		return $this;
	}

	/**
	 * SELECT
	 *
	 * @param mixed $columns
	 *
	 * @return Command
	 */
	public function select($columns = '*')
	{
		///
		if (is_string($columns) && strpos($columns, '(') !== false)
			$this->query['select'] = $columns;
		else {
			if (! is_array($columns))
				$columns = preg_split('/\s*,\s*/', trim($columns), - 1, PREG_SPLIT_NO_EMPTY);

			///
			foreach ($columns as $i => $column) {
				if (is_object($column))
					$columns[$i] = (string)$column;
				else if (strpos($column, '(') === false) {
					if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)(.*)$/', $column, $m))
						$columns[$i] = $this->pdo->quoteColumnName($m[1]) . ' AS ' . $this->pdo->quoteColumnName($m[2]);
					else
						$columns[$i] = $this->pdo->quoteColumnName($column);
				}
			}
			$this->query['select'] = implode(', ', $columns);
		}

		return $this;
	}

	/**
	 * SELECT
	 *
	 * @param mixed $columns
	 *
	 * @return Command
	 */
	public function selectDistinct($columns = '*')
	{
		///
		$this->query['distinct'] = true;

		return $this->select($columns);
	}

	/**
	 * FROM
	 *
	 * @param mixed $tables
	 *
	 * @return Command
	 */
	public function from($tables)
	{
		///
		if (is_string($tables) && strpos($tables, '(') !== false)
			$this->query['from'] = $tables;
		else {
			if (! is_array($tables))
				$tables = preg_split('/\s*,\s*/', trim($tables), - 1, PREG_SPLIT_NO_EMPTY);

			///
			foreach ($tables as $i => $table) {
				if (strpos($table, '(') === false) {
					if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)(.*)$/', $table, $m)) // with alias
						$tables[$i] = $this->pdo->quoteTableName($m[1]) . ' ' . $this->pdo->quoteTableName($m[2]);
					else
						$tables[$i] = $this->pdo->quoteTableName($table);
				}
			}
			$this->query['from'] = implode(', ', $tables);
		}

		return $this;
	}

	/**
	 * WHERE
	 *
	 * @param mixed $conditions
	 * @param array $params
	 *
	 * @return Command
	 */
	public function where($conditions, $params = array())
	{
		///
		$this->query['where'] = $this->_processConditions($conditions);
		foreach ($params as $name => $value)
			$this->params[$name] = $value;

		return $this;
	}

	/**
	 * JOIN
	 *
	 * @param string $table
	 * @param mixed $conditions
	 * @param array $params
	 *
	 * @return Command
	 */
	public function join($table, $conditions, $params = array())
	{
		///
		return $this->_joinInternal('join', $table, $conditions, $params);
	}

	/**
	 * JOIN
	 *
	 * @param string $table
	 * @param mixed $conditions
	 * @param array $params
	 *
	 * @return Command
	 */
	public function leftJoin($table, $conditions, $params = array())
	{
		///
		return $this->_joinInternal('left join', $table, $conditions, $params);
	}

	/**
	 * JOIN
	 *
	 * @param string $table
	 * @param mixed $conditions
	 * @param array $params
	 *
	 * @return Command
	 */
	public function rightJoin($table, $conditions, $params = array())
	{
		///
		return $this->_joinInternal('right join', $table, $conditions, $params);
	}

	/**
	 * GROUP
	 *
	 * @param mixed $columns
	 *
	 * @return Command
	 */
	public function group($columns)
	{
		///
		if (is_string($columns) && strpos($columns, '(') !== false)
			$this->query['group'] = $columns;
		else {
			if (! is_array($columns))
				$columns = preg_split('/\s*,\s*/', trim($columns), - 1, PREG_SPLIT_NO_EMPTY);

			///
			foreach ($columns as $i => $column) {
				if (is_object($column))
					$columns[$i] = (string)$column;
				else if (strpos($column, '(') === false)
					$columns[$i] = $this->pdo->quoteColumnName($column);
			}
			$this->query['group'] = implode(', ', $columns);
		}

		return $this;
	}

	/**
	 * HAVING
	 *
	 * @param mixed $conditions
	 * @param array $params
	 *
	 * @return Command
	 */
	public function having($conditions, $params = array())
	{
		///
		$this->query['having'] = $this->_processConditions($conditions);
		foreach ($params as $name => $value)
			$this->params[$name] = $value;

		return $this;
	}

	/**
	 * ORDER
	 *
	 * @param mixed $columns
	 *
	 * @return Command
	 */
	public function order($columns)
	{
		///
		if (is_string($columns) && strpos($columns, '(') !== false)
			$this->query['order'] = $columns;
		else {
			if (! is_array($columns))
				$columns = preg_split('/\s*,\s*/', trim($columns), - 1, PREG_SPLIT_NO_EMPTY);

			///
			foreach ($columns as $i => $column) {
				if (is_object($column))
					$columns[$i] = (string)$column;
				else if (strpos($column, '(') === false) {
					if (preg_match('/^(.*?)\s+(asc|desc)$/i', $column, $m))
						$columns[$i] = $this->pdo->quoteColumnName($m[1]) . ' ' . strtoupper($m[2]);
					else
						$columns[$i] = $this->pdo->quoteColumnName($column);
				}
			}
			$this->query['order'] = implode(', ', $columns);
		}

		return $this;
	}

	/**
	 * LIMIT & OFFSET
	 *
	 * @param int $limit
	 * @param int $offset
	 *
	 * @return Command
	 */
	public function limit($limit, $offset = null)
	{
		///
		$this->query['limit'] = (int)$limit;
		if ($offset !== null)
			$this->query['offset'] = (int)$offset;

		return $this;
	}

	/**
	 * UNION
	 *
	 * @param string $sql
	 *
	 * @return Command
	 */
	public function union($sql)
	{
		///
		if (isset($this->query['union']) && is_string($this->query['union']))
			$this->query['union'] = array($this->query['union']);

		$this->query['union'][] = $sql;

		return $this;
	}

	/**
	 * INSERT
	 *
	 * @param string $table
	 * @param array $columns
	 *
	 * @return int
	 */
	public function insert($table, $columns)
	{
		///
		$names = array();
		$placeholders = array();
		foreach ($columns as $name => $value) {
			$names[] = $this->pdo->quoteColumnName($name);
			if ($value instanceof Expression) {
				$placeholders[] = $value->expression;
				foreach ($value->params as $n => $v)
					$this->params[$n] = $v;
			}
			else {
				$placeholders[] = ':' . $name;
				$this->params[':' . $name] = $value;
			}
		}
		$sql = 'INSERT INTO ' . $this->pdo->quoteTableName($table)
			. ' (' . implode(', ', $names) . ') VALUES ('
			. implode(', ', $placeholders) . ')';

		return $this->setText($sql);
	}

	/**
	 * UPDATE
	 *
	 * @param string $table
	 * @param mixed $columns
	 * @param mixed $conditions
	 *
	 * @return int
	 */
	public function update($table, $columns, $conditions = '')
	{
		///
		$lines = array();
		foreach ($columns as $name => $value) {
			if ($value instanceof Expression) {
				$lines[] = $this->pdo->quoteColumnName($name) . '=' . $value->expression;
				foreach ($value->params as $n => $v)
					$this->params[$n] = $v;
			}
			else {
				$lines[] = $this->pdo->quoteColumnName($name) . '=:' . $name;
				$this->params[':' . $name] = $value;
			}
		}
		$sql = 'UPDATE ' . $this->pdo->quoteTableName($table) . ' SET ' . implode(', ', $lines);
		if (($where = $this->_processConditions($conditions)) != '')
			$sql .= ' WHERE ' . $where;

		return $this->setText($sql);
	}

	/**
	 * DELETE
	 *
	 * @param string $table
	 * @param mixed $conditions
	 *
	 * @return int
	 */
	public function delete($table, $conditions = '')
	{
		///
		$sql = 'DELETE FROM ' . $this->pdo->quoteTableName($table);
		if (($where = $this->_processConditions($conditions)) != '')
			$sql .= ' WHERE ' . $where;

		return $this->setText($sql);
	}

	/**
	 * 构建SELECT语句
	 *
	 * @throws Exception
	 * @internal param array $query
	 *
	 * @return string
	 */
	private function _buildQuery()
	{
		///
		$query = $this->query;

		/// SELECT
		$sql = isset($query['distinct']) && $query['distinct'] ? 'SELECT DISTINCT' : 'SELECT';
		$sql .= ' ' . (isset($query['select']) ? $query['select'] : '*');

		/// FROM
		if (isset($query['from']))
			$sql .= "\nFROM " . $query['from'];
		else
			throw new Exception('[Command] The DB query must contain the "from" portion.');

		/// JOIN
		if (isset($query['join']))
			$sql .= "\n" . (is_array($query['join']) ? implode("\n", $query['join']) : $query['join']);

		/// WHERE
		if (isset($query['where']))
			$sql .= "\nWHERE " . $query['where'];

		/// GROUP
		if (isset($query['group']))
			$sql .= "\nGROUP BY " . $query['group'];

		/// HAVING
		if (isset($query['having']))
			$sql .= "\nHAVING " . $query['having'];

		/// ORDER
		if (isset($query['order']))
			$sql .= "\nORDER BY " . $query['order'];

		/// LIMIT & OFFSET
		$limit = isset($query['limit']) ? (int)$query['limit'] : - 1;
		$offset = isset($query['offset']) ? (int)$query['offset'] : - 1;
		if ($limit >= 0)
			$sql .= ' LIMIT ' . (int)$limit;
		if ($offset > 0)
			$sql .= ' OFFSET ' . (int)$offset;

		/// UNION
		if (isset($query['union']))
			$sql .= "\nUNION (\n" .
				(is_array($query['union']) ? implode("\n) UNION (\n", $query['union']) : $query['union']) . ')';

		return $sql;
	}

	/**
	 * 构造连接操作
	 *
	 * @param string $type
	 * @param string $table
	 * @param mixed $conditions
	 * @param array $params
	 *
	 * @return Command
	 */
	private function _joinInternal($type, $table, $conditions = '', $params = array())
	{
		///
		if (strpos($table, '(') === false) {
			if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)(.*)$/', $table, $m)) // with alias
				$table = $this->pdo->quoteTableName($m[1]) . ' ' . $this->pdo->quoteTableName($m[2]);
			else
				$table = $this->pdo->quoteTableName($table);
		}

		$conditions = $this->_processConditions($conditions);
		if ($conditions != '')
			$conditions = ' ON ' . $conditions;

		if (isset($this->query['join']) && is_string($this->query['join']))
			$this->query['join'] = array($this->query['join']);

		$this->query['join'][] = strtoupper($type) . ' ' . $table . $conditions;

		foreach ($params as $name => $value)
			$this->params[$name] = $value;

		return $this;
	}

	/**
	 * 构造搜索条件
	 *
	 * @param mixed $conditions
	 *
	 * @throws \Exception
	 * @return string
	 */
	private function _processConditions($conditions)
	{
		///
		if (! is_array($conditions))
			return $conditions;
		else if ($conditions === array())
			return '';

		///
		$n = count($conditions);
		$operator = strtoupper($conditions[0]);
		if ($operator === 'OR' || $operator === 'AND') {
			$parts = array();
			for ($i = 1; $i < $n; ++$i) {
				$condition = $this->_processConditions($conditions[$i]);
				if ($condition !== '')
					$parts[] = '(' . $condition . ')';
			}

			return $parts === array() ? '' : implode(' ' . $operator . ' ', $parts);
		}

		///
		if (! isset($conditions[1], $conditions[2]))
			return '';

		$column = $conditions[1];
		if (strpos($column, '(') === false)
			$column = $this->pdo->quoteColumnName($column);

		$values = $conditions[2];
		if (! is_array($values))
			$values = array($values);

		/// IN
		if ($operator === 'IN' || $operator === 'NOT IN') {
			if ($values === array())
				return $operator === 'IN' ? '0=1' : '';
			foreach ($values as $i => $value) {
				if (is_string($value))
					$values[$i] = $this->pdo->quoteValue($value);
				else
					$values[$i] = (string)$value;
			}

			return $column . ' ' . $operator . ' (' . implode(', ', $values) . ')';
		}

		/// LIKE
		if ($operator === 'LIKE' || $operator === 'NOT LIKE'
			|| $operator === 'OR LIKE' || $operator === 'OR NOT LIKE'
		) {
			if ($values === array())
				return $operator === 'LIKE' || $operator === 'OR LIKE' ? '0=1' : '';

			if ($operator === 'LIKE' || $operator === 'NOT LIKE')
				$andor = ' AND ';
			else {
				$andor = ' OR ';
				$operator = $operator === 'OR LIKE' ? 'LIKE' : 'NOT LIKE';
			}
			$expressions = array();
			foreach ($values as $value)
				$expressions[] = $column . ' ' . $operator . ' ' . $this->pdo->quoteValue($value);

			return implode($andor, $expressions);
		}

		throw new Exception('[Command] Unknown operator "{operator}".');
	}
}
